package org.datascooter.impl;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;

import org.datascooter.bundle.ContainerProvider;
import org.datascooter.bundle.CrossTable;
import org.datascooter.bundle.DSMapper;
import org.datascooter.bundle.EntityBundle;
import org.datascooter.bundle.EntityPart;
import org.datascooter.bundle.attribute.DBIndex;
import org.datascooter.bundle.attribute.PersistAttribute;
import org.datascooter.bundle.attribute.PersistReferenceAttribute;
import org.datascooter.exception.DataScooterException;
import org.datascooter.exception.EntityNotMappedException;
import org.datascooter.inface.IDataManager;
import org.datascooter.inface.ISnipBuilder;
import org.datascooter.meta.Column;
import org.datascooter.meta.MetaColumn;
import org.datascooter.meta.MetaLink;
import org.datascooter.utils.DBType;
import org.datascooter.utils.DSSettings;
import org.datascooter.utils.LangUtils;
import org.datascooter.utils.SnipUtils;
import org.datascooter.utils.TypeUtils;
import org.datascooter.utils.policy.Constraint;
import org.datascooter.utils.policy.Function;
import org.datascooter.utils.policy.SnipType;

/**
 * Ancestor of other context-depended builders purposed for a build Snip from info contained in DataSnip
 * 
 * @author nemo
 * 
 */
public abstract class SnipBuilder implements ISnipBuilder {

	private static final String T = "t";
	private final String contextId;
	protected StringBuilder columns;
	protected StringBuilder tables;
	protected StringBuilder commaColumns;
	protected StringBuilder commaTokens;
	protected StringBuilder where;
	protected StringBuilder values;
	protected Map<String, String> aliasMap = new HashMap<String, String>();
	protected List<Object> params = new ArrayList<Object>();
	protected int aliasIndex = 0;
	private ContainerProvider crossProvider;

	public abstract ISnipBuilder copy();

	public SnipBuilder(String context) {
		contextId = context;
	}

	public void init(DataSnip dataSnip) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		initBuilders();
		initInt(dataSnip);
	}

	@Override
	public void setContainerProvider(ContainerProvider crossProvider) {
		this.crossProvider = crossProvider;
	}

	protected void initBuilders() {
		columns = new StringBuilder();
		tables = new StringBuilder();
		commaColumns = new StringBuilder();
		commaTokens = new StringBuilder();
		where = new StringBuilder();
		values = new StringBuilder();
		aliasMap.clear();
		params.clear();
		aliasIndex = 0;
	}

	protected void clear() {
		columns = null;
		tables = null;
		commaColumns = null;
		commaTokens = null;
		where = null;
		values = null;
		aliasMap.clear();
		params.clear();
		aliasIndex = 0;
	}

	public void initInt(DataSnip snip) throws EntityNotMappedException {
		if (snip.entityName != null && snip.bundle == null) {
			snip.setBundle(DSMapper.getBundle(snip.entityName));
		}
		for (DataSnip dataSnip : snip.joinList) {
			initInt(dataSnip);
		}
		for (DataSnip dataSnip : snip.fetchList) {
			initInt(dataSnip);
		}
	}

	public DataSnip createEmptyEntitySnip(String entity) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		DataSnip result = DataSnipFactory.none(entity);
		for (PersistAttribute attribute : DSMapper.getBundle(entity).attrArray) {
			result.what(attribute.name, null);
		}
		return result;
	}

	// @Override
	// public String createSequence() {
	// return "";
	// }
	public Snip updateSnipInt(Snip snip, String transactionId) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		EntityBundle bundle = DSMapper.getBundle(snip.entity);
		DataSnip dataSnip = createEmptyEntitySnip(snip.entity);
		dataSnip.setSnipType(SnipType.UPDATE);
		dataSnip.whereId(snip.getData()[0][0]);
		String[] attrArray = bundle.attrNameArray;
		dataSnip.what = null;
		for (int a = 1; a < attrArray.length; a++) {
			dataSnip.what(attrArray[a], snip.getData()[0][a]);
		}
		Snip build = build(dataSnip, transactionId);
		Snip[] fetch = snip.getFetch();
		if (fetch != null && fetch.length > 0) {
			Snip[] newFetch = new Snip[fetch.length];
			for (int a = 0; a < fetch.length; a++) {
				newFetch[a] = updateSnipInt(fetch[a], transactionId);
			}
			build.setFetch(newFetch);
		}
		return build;
	}

	public Snip saveSnipInt(Snip snip, String transactionId) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		DataSnip dataSnip = createEmptyEntitySnip(snip.entity);
		dataSnip.setSnipType(SnipType.INSERT);
		Snip build = build(dataSnip, transactionId);
		build.setData(snip.getData());
		Snip[] fetch = snip.getFetch();
		if (fetch != null && fetch.length > 0) {
			Snip[] newFetch = new Snip[fetch.length];
			for (int a = 0; a < fetch.length; a++) {
				newFetch[a] = saveSnipInt(fetch[a], transactionId);
			}
			build.setFetch(newFetch);
		}
		return build;
	}

	public Snip deleteSnipInt(Snip snip, String transactionId) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		DataSnip dataSnip = DataSnipFactory.delete(snip.entity);
		EntityBundle bundle = DSMapper.getBundle(snip.entity);
		for (int a = 0; a < snip.getData().length; a++) {
			dataSnip.where(bundle.id.name, snip.getData()[a][0]);
		}
		Snip build = build(dataSnip, transactionId);
		Snip[] fetch = snip.getFetch();
		if (fetch != null && fetch.length > 0) {
			Snip[] newFetch = new Snip[fetch.length];
			for (int a = 0; a < fetch.length; a++) {
				newFetch[a] = deleteSnipInt(fetch[a], transactionId);
			}
			build.setFetch(newFetch);
		}
		return build;
	}

	public <T> Snip getCollectionSnip(Collection<?> input, SnipType type, String transactionId, IDataManager dataManager) {
		Snip snip = null;
		try {
			Object first = input.iterator().next();
			EntityBundle bundle = DSMapper.getBundle(first);
			Object[] inputArray = input.toArray();
			Object[][] dataArray = new Object[inputArray.length][];
			if (type.equals(SnipType.DELETE)) {
				snip = build(DataSnipFactory.delete(first.getClass().getName()).whereId(0), transactionId);
				PersistAttribute id = bundle.id;
				for (int a = 0; a < inputArray.length; a++) {
					dataArray[a] = new Object[] {
						id.getValue(inputArray[a])
					};
				}
			} else if (type.equals(SnipType.INSERT)) {
				snip = build(dataManager.getInstanceManager().createInsertSnip(first, bundle), transactionId);
				for (int a = 0; a < inputArray.length; a++) {
					dataArray[a] = bundle.flush(inputArray[a], dataManager);
				}
			} else if (type.equals(SnipType.UPDATE)) {
				DataSnip dataSnip = dataManager.getInstanceManager().createInsertSnip(first, bundle);
				dataSnip.setSnipType(type);
				dataSnip.whereId(DSMapper.getIdValue(first));
				snip = build(dataSnip, transactionId);
				for (int a = 0; a < inputArray.length; a++) {
					Object[] array = bundle.flush(inputArray[a], dataManager);
					Object[] result = new Object[array.length];
					System.arraycopy(array, 1, result, 0, array.length - 1);
					result[array.length - 1] = array[0];
					dataArray[a] = result;
				}
			}
			if (snip != null) {
				snip.setData(dataArray);
			}
		} catch (Exception e) {
			throw new DataScooterException("***Error by col;lection process: ", e);
		}
		return snip;
	}

	protected String getTableAlias(String tableName) {
		String alias = aliasMap.get(tableName);
		if (alias == null) {
			aliasIndex++;
			alias = T + aliasIndex; //$NON-NLS-1$
			aliasMap.put(tableName, alias);
		}
		return alias;
	}

	protected String getTableAlias(DataSnip dataSnip) {
		return getTableAlias(dataSnip.bundle.getTableName());
	}

	protected String getTableAlias(EntityBundle bundle) {
		return getTableAlias(bundle.getTableName());
	}

	protected void buildTablesClause(DataSnip dataSnip) throws EntityNotMappedException {
		String string = getCaseSensitiveTableName(dataSnip.bundle.getTableName()) + SnipUtils.SP + getTableAlias(dataSnip);
		if (tables.lastIndexOf(string) == -1) {
			if (tables.length() > 0) {
				tables.append(SnipUtils.COMMA);
			}
			tables.append(string);
		}
		for (DataSnip snip : dataSnip.joinList) {
			buildTablesClause(snip);
		}
	}

	protected String buildFunction(DataSnip dataSnip) throws EntityNotMappedException {
		buildTablesClause(dataSnip);
		buildWhereClause(dataSnip);
		String field = LangUtils.isEmptyString(dataSnip.extraAttribute) ? SnipUtils.NN : dataSnip.bundle.getAttribute(dataSnip.extraAttribute).getColumnName();
		return MessageFormat.format(SnipUtils.SELECT_FROM, Function.toString(dataSnip.getFunction(), field), SnipUtils.NN, tables.toString(), where.toString());
	}

	protected String buildDelete(DataSnip dataSnip) throws EntityNotMappedException {
		if (hasJoin(dataSnip)) {
			buildTablesClause(dataSnip);
			buildWhereClause(dataSnip);
			return MessageFormat.format(SnipUtils.DELETE_FROM1, getTableAlias(dataSnip) + SnipUtils.DOT_ASTERICS, tables.toString(), where.toString());
		} else {
			buildWhereClause(dataSnip);
			return MessageFormat.format(SnipUtils.DELETE_FROM, getCaseSensitiveTableName(dataSnip.bundle.getTableName()), SnipUtils.NN, where.toString());
		}
	}

	protected String buildInsert(DataSnip dataSnip) throws EntityNotMappedException {
		buildCommaColumnsAndTokensClause(dataSnip);
		return MessageFormat.format(SnipUtils.INSERT_INTO, getCaseSensitiveTableName(dataSnip.bundle.getTableName()), commaColumns.toString(), commaTokens.toString());
	}

	public String buildSelect(DataSnip dataSnip) throws EntityNotMappedException {
		buildColumnsClause(dataSnip);
		buildTablesClause(dataSnip);
		buildWhereClause(dataSnip);
		if (dataSnip.orderASC != null) {
			where.append(SnipUtils.ORDER_BY);
			where.append(SnipUtils.SP);
			where.append(getTableAlias(dataSnip));
			where.append(SnipUtils.DOT);
			where.append(dataSnip.bundle.getAttribute(dataSnip.extraAttribute).getColumnName());
			where.append(SnipUtils.SP);
			where.append(dataSnip.orderASC ? SnipUtils.ASC : SnipUtils.DESC);
		}
		return MessageFormat.format(SnipUtils.SELECT_FROM, (dataSnip.distinct ? SnipUtils.DISTINCT : SnipUtils.NN), columns.toString(), tables.toString(), where.toString());
	}

	protected String buildUpdate(DataSnip dataSnip) throws EntityNotMappedException {
		buildTablesClause(dataSnip);
		buildUpdateValuesClause(dataSnip);
		buildWhereClause(dataSnip);
		return MessageFormat.format(SnipUtils.UPDATE_WHERE, getCaseSensitiveTableName(dataSnip.bundle.getTableName()), values.toString(), where.toString());
	}

	protected String buildDrop(DataSnip dataSnip) {
		return MessageFormat.format(SnipUtils.DROP_TABLE, getCaseSensitiveTableName(dataSnip.bundle.getTableName()));
	}

	@SuppressWarnings("nls")
	protected void buildWhereClause(DataSnip dataSnip) throws EntityNotMappedException {
		if (dataSnip.bundle.discriminator != null) {
			// dataSnip.and().addChild(dataSnip.bundle.discriminator);
			dataSnip.where(dataSnip.bundle.discriminator);
		}
		buildWhereInt(dataSnip);
		if (dataSnip.byResult) {
			getJoinFetch(dataSnip);
		}
		if (where.length() > 0) {
			where.insert(0, SnipUtils.WHERE);
		}
	}

	protected void buildWhereInt(DataSnip dataSnip) throws EntityNotMappedException {
		if (dataSnip.where != null) {
			buildConditionClause(dataSnip, dataSnip.where, 0);
		}
		for (DataSnip snip : dataSnip.joinList) {
			String join = getJoin(dataSnip, snip);
			if (join.length() > 0) {
				if (where.length() > 0) {
					where.append(SnipUtils.AND);
				}
				where.append(join);
			}
			buildWhereInt(snip);
		}
	}

	private void buildConditionClause(DataSnip dataSnip, Condition condition, int parentCount) {
		int count = 0;
		for (LogicValue logicValue : condition.valueList) {
			if ((where.length() > 0) && ((parentCount == 0) || (count > 0))) {
				where.append(SnipUtils.SP);
				where.append(condition.operation.name());
				where.append(SnipUtils.SP);
			}
			PersistAttribute columnByAttribute = dataSnip.bundle.getAttribute(logicValue.attribute);
			if (columnByAttribute != null) {
				getTableAliasWhere(dataSnip);
				where.append(dataSnip.bundle.getAttribute(logicValue.attribute).getColumnName());
				where.append(SnipUtils.SP);
				checkParamValue(columnByAttribute, logicValue);
			} else {
				throw new DataScooterException("***What's wrong with you query? In bundle " + dataSnip.bundle.entity + "  not found attribute " + logicValue.attribute);
			}
			count++;
		}
		for (Condition child : condition.childList) {
			if ((condition.valueList.size() > 0)) {
				where.append(SnipUtils.SP);
				where.append(child.operation.name());
				where.append(SnipUtils.LB);
			}
			buildConditionClause(dataSnip, child, 1 + parentCount);
			if (condition.valueList.size() > 0) {
				where.append(SnipUtils.RB);
			}
		}
	}

	protected void getTableAliasWhere(DataSnip dataSnip) {
		if (dataSnip.getSnipType().equals(SnipType.INSERT) || dataSnip.getSnipType().equals(SnipType.UPDATE) || dataSnip.getSnipType().equals(SnipType.DELETE)) {
			if (hasJoin(dataSnip)) {
				where.append(getTableAlias(dataSnip));
				where.append(SnipUtils.DOT);
			}
		} else {
			where.append(getTableAlias(dataSnip));
			where.append(SnipUtils.DOT);
		}
	}

	protected boolean hasJoin(DataSnip dataSnip) {
		if (dataSnip.joinList.size() > 0) {
			return true;
		} else {
			if (dataSnip.parent != null) {
				return hasJoin(dataSnip.parent);
			}
		}
		return false;
	}

	@SuppressWarnings("nls")
	protected void checkParamValue(PersistAttribute columnByAttribute, LogicValue logicValue) {
		if (logicValue.getValue() == null) {
			if (logicValue.constraint.equals(Constraint.EQUAL)) {
				where.append(SnipUtils.IS_NULL);
			} else if (logicValue.constraint.equals(Constraint.NOT_EQUAL)) {
				where.append(SnipUtils.IS_NOT_NULL);
			}
		} else {
			where.append(Constraint.toString(logicValue.constraint));
			if (logicValue.constraint.equals(Constraint.LIKE) || logicValue.constraint.equals(Constraint.NOT_LIKE)) {
				where.append(SnipUtils.LBQURB);
			} else {
				where.append(SnipUtils.QU);
			}
			addParam(columnByAttribute.getType(), logicValue.getValue());
		}
	}

	protected void getJoinFetch(DataSnip child) throws EntityNotMappedException {
		String childRef = null;
		String parentRef = null;
		EntityBundle childBundle = child.bundle;
		EntityBundle parentBundle = child.parent.bundle;
		if (child.extraAttribute != null && child.extraAttribute.length() > 0) {
			if (child.joinFromChild) {
				PersistAttribute columnByAttribute = childBundle.getAttribute(child.extraAttribute);
				childRef = columnByAttribute == null ? null : columnByAttribute.getColumnName();
			} else {
				PersistAttribute columnByAttribute = parentBundle.getAttribute(child.extraAttribute);
				parentRef = columnByAttribute == null ? null : columnByAttribute.getColumnName();
			}
		} else {
			PersistReferenceAttribute childReference = childBundle.getEntityReference(parentBundle.entity);
			childRef = childReference == null ? null : childReference.getColumnName();
			PersistReferenceAttribute entityReference = parentBundle.getEntityReference(childBundle.entity);
			parentRef = entityReference == null ? null : entityReference.getColumnName();
		}
		if (childRef != null && parentRef == null) {
			child.parentIndex = 0;// index of id is allways equal zero
			where.append(getTableAlias(childBundle));
			where.append(SnipUtils.DOT);
			where.append(childRef);
			where.append(SnipUtils.EQQU);
		} else if (parentRef != null && childRef == null) {
			child.parentIndex = parentBundle.getColumnIndex(parentRef);
			where.append(getTableAlias(childBundle));
			where.append(SnipUtils.DOT);
			where.append(parentBundle.id.getColumnName());
			where.append(SnipUtils.EQQU);
		} else {
			// join one to one by id
			child.parentIndex = 0;// index of id is allways equal zero
			where.append(getTableAlias(childBundle));
			where.append(SnipUtils.DOT);
			where.append(childBundle.id.getColumnName());
			where.append(SnipUtils.EQQU);
		}
	}

	protected String getJoin(DataSnip dataSnip, DataSnip child) {
		PersistAttribute childRefAttr = resolveChildReference(dataSnip.bundle, child.bundle, child);
		PersistAttribute parentRefAttr = resolveParentReference(dataSnip.bundle, child.bundle, child);
		if (childRefAttr != null && parentRefAttr == null) {
			return fillJoin(dataSnip.bundle, child.bundle, childRefAttr);
		} else if (parentRefAttr != null && childRefAttr == null) {
			return fillJoin(child.bundle, dataSnip.bundle, parentRefAttr);
		} else {
			if (child.extraAttribute.equals(SnipUtils.NN)) {
				return getContentOutQuery(dataSnip, child);
			} else {
				if (child.joinFromChild) {
					return fillJoin(dataSnip.bundle, child.bundle, child.bundle.getAttribute(child.extraAttribute));
				} else {
					return MessageFormat.format(SnipUtils.JOIN, getTableAlias(child), child.bundle.id.getColumnName(), getTableAlias(dataSnip), dataSnip.bundle.getAttribute(child.extraAttribute).getColumnName());
				}
			}
		}
	}

	public String getContentOutQuery(DataSnip dataSnip, DataSnip child) {
		try {
			Map<String, CrossTable> map = crossProvider.getCrossMapOut(dataSnip.entityName);
			if (map != null) {
				CrossTable targetTable = null;
				for (CrossTable table : map.values()) {
					if (table.getContentClass().equals(child.entityName)) {
						targetTable = table;
						break;
					}
				}
				if (targetTable != null) {
					EntityBundle crossBundle = DSMapper.getBundle(targetTable.getTableName());
					addTable(crossBundle.getTableName());
					return MessageFormat.format(SnipUtils.JOIN, getTableAlias(dataSnip), dataSnip.bundle.id.getColumnName(), getTableAlias(crossBundle), crossBundle.getAttribute(ContainerProvider.CONTAINER_ID).getColumnName())
						+ SnipUtils.AND + MessageFormat.format(SnipUtils.JOIN, getTableAlias(crossBundle), crossBundle.getAttribute(ContainerProvider.CONTENT_ID).getColumnName(), getTableAlias(child), child.bundle.id.getColumnName());
				}
			}
			map = crossProvider.getCrossMapOut(child.entityName);
			if (map != null) {
				CrossTable targetTable = null;
				for (CrossTable table : map.values()) {
					if (table.getContentClass().equals(dataSnip.entityName)) {
						targetTable = table;
						break;
					}
				}
				if (targetTable != null) {
					EntityBundle crossBundle = DSMapper.getBundle(targetTable.getTableName());
					addTable(crossBundle.getTableName());
					return MessageFormat.format(SnipUtils.JOIN, getTableAlias(child), child.bundle.id.getColumnName(), getTableAlias(crossBundle), crossBundle.getAttribute(ContainerProvider.CONTAINER_ID).getColumnName()) + SnipUtils.AND
						+ MessageFormat.format(SnipUtils.JOIN, getTableAlias(crossBundle), crossBundle.getAttribute(ContainerProvider.CONTENT_ID).getColumnName(), getTableAlias(dataSnip), dataSnip.bundle.id.getColumnName());
				}
			}
			return SnipUtils.NN;
		} catch (Exception e) {
			throw new DataScooterException(e);
		}
	}

	private void addTable(String tableName) {
		String string = tableName + SnipUtils.SP + getTableAlias(tableName);
		if (tables.lastIndexOf(string) == -1) {
			if (tables.length() > 0) {
				tables.append(SnipUtils.COMMA);
			}
			tables.append(string);
		}
	}

	private String fillJoin(EntityBundle parentBundle, EntityBundle childBundle, PersistAttribute refAttrinute) {
		return MessageFormat.format(SnipUtils.JOIN, getTableAlias(parentBundle), parentBundle.id.getColumnName(), getTableAlias(childBundle), refAttrinute.getColumnName());
	}

	protected PersistAttribute resolveChildReference(EntityBundle parentBundle, EntityBundle childBundle, DataSnip child) {
		if (child.extraAttribute.length() > 0) {
			if (child.joinFromChild) {
				return childBundle.getAttribute(child.extraAttribute);
			} else {
				return parentBundle.getAttribute(child.extraAttribute);
			}
		} else {
			return childBundle.getEntityReference(parentBundle.entity);
		}
	}

	protected PersistAttribute resolveParentReference(EntityBundle parentBundle, EntityBundle childBundle, DataSnip child) {
		if (child.extraAttribute.length() > 0) {
			if (!child.joinFromChild) {
				return parentBundle.getAttribute(child.extraAttribute);
			} else {
				return childBundle.getAttribute(child.extraAttribute);
			}
		} else {
			return parentBundle.getEntityReference(childBundle.entity);
		}
	}

	protected void buildColumnsClause(DataSnip snip) {
		if (snip.extraList.size() == 0) {
			columns.append(getTableAlias(snip));
			columns.append(SnipUtils.DOT_ASTERICS);
		} else {
			for (String column : snip.columnNames) {
				if (columns.length() > 0) {
					columns.append(SnipUtils.COMMA);
				}
				columns.append(getTableAlias(snip));
				columns.append(SnipUtils.DOT);
				columns.append(column);
			}
		}
	}

	protected void buildUpdateValuesClause(DataSnip dataSnip) {
		Condition condition = dataSnip.what;
		if (condition != null) {
			for (int a = 0; a < condition.valueList.size(); a++) {
				LogicValue logicValue = condition.valueList.get(a);
				PersistAttribute attribute = dataSnip.bundle.getAttribute(logicValue.attribute);
				if (attribute.isUpdatable()) {
					if (values.length() > 0) {
						values.append(SnipUtils.COMMA);
					}
					values.append(attribute.getColumnName() + SnipUtils.EQQU);
					addParam(attribute.getType(), logicValue.getValue());
				}
			}
		}
	}

	@SuppressWarnings("nls")
	protected void buildCommaColumnsAndTokensClause(DataSnip dataSnip) throws EntityNotMappedException {
		Condition condition = dataSnip.what;
		if (condition != null) {
			for (int a = 0; a < condition.valueList.size(); a++) {
				LogicValue logicValue = condition.valueList.get(a);
				if (commaColumns.length() > 0) {
					commaColumns.append(SnipUtils.COMMA);
					commaTokens.append(SnipUtils.COMMA);
				}
				PersistAttribute attribute = dataSnip.bundle.getAttribute(logicValue.attribute);
				if (attribute == null) {
					continue;
				}
				commaColumns.append(attribute.getColumnName());
				commaTokens.append(SnipUtils.QU);
				addParam(attribute.getType(), logicValue.getValue());
			}
		}
	}

	@SuppressWarnings("nls")
	protected void buildCreateColumnsClause(DataSnip dataSnip) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		Set<String> columnSet = new HashSet<String>();
		for (PersistAttribute attribute : dataSnip.bundle.attrArray) {
			if (columnSet.add(attribute.getColumnName())) {
				if (columns.length() > 0) {
					columns.append(SnipUtils.COMMA);
					columns.append(SnipUtils.NL);
				}
				try {
					columns.append(buildColumnClause(attribute));
				} catch (Exception e) {
					Logger.getLogger(this.getClass().getName()).info("***Error by create clause for " + attribute.name);
					throw new DataScooterException(e);
				}
			}
		}
		if (dataSnip.bundle.hasEmbeddedEntities) {
			for (EntityBundle embeded : dataSnip.bundle.embeddedEntityList) {
				for (PersistAttribute attribute : embeded.attrArray) {
					if (columnSet.add(attribute.getColumnName())) {
						if (columns.length() > 0) {
							columns.append(SnipUtils.COMMA);
							columns.append(SnipUtils.NL);
						}
						columns.append(buildColumnClause(attribute));
					}
				}
			}
		}
	}

	protected void buildKeysClause(DataSnip dataSnip) throws EntityNotMappedException {
		if (dataSnip.bundle.id != null) {
			commaTokens.append(MessageFormat.format(
				SnipUtils.ALTER_TABLE,
				getCaseSensitiveTableName(dataSnip.bundle.getTableName()),
				LangUtils.abbreviate(dataSnip.bundle.table, 6, SnipUtils.UNDERLINE, 2),
				LangUtils.getRandomIndexString(4),
				dataSnip.bundle.id.getColumnName()));
		}
	}

	@SuppressWarnings("nls")
	protected void buildIndexClause(DataSnip dataSnip) {
		buildPrimaryIndex(dataSnip);
		for (DBIndex dbIndex : dataSnip.bundle.indexList) {
			StringBuilder builder = new StringBuilder("");
			for (PersistAttribute attribute : dbIndex.getAttributeList()) {
				if (builder.length() > 0) {
					builder.append(SnipUtils.COMMA);
					builder.append(SnipUtils.SP);
				}
				builder.append(attribute.getColumnName());
			}
			buildColumnIndex(commaColumns, dbIndex.name, dbIndex.type, builder.toString(), getCaseSensitiveTableName(dataSnip.bundle.getTableName()));
		}
	}

	@Override
	public void buildPrimaryIndex(DataSnip dataSnip) {
		if (dataSnip.bundle.id != null) {
			buildColumnIndex(commaColumns, null, null, dataSnip.bundle.id.getColumnName(), getCaseSensitiveTableName(dataSnip.bundle.getTableName()));
		}
	}

	@SuppressWarnings("nls")
	protected void buildColumnIndex(StringBuilder indexBuilder, String name, String type, String column, String tableName) {
		if (indexBuilder.length() > 0) {
			indexBuilder.append(SnipUtils.SEMICOLON + SnipUtils.NL);
		}
		indexBuilder.append(MessageFormat.format(SnipUtils.CREATE_INDEX, LangUtils.defaultIfEmpty(name, SnipUtils.NN).toUpperCase() + LangUtils.getRandomIndexString(3), LangUtils.defaultIfEmpty(type), tableName, column));
	}

	public void setAliasIndex(int index) {
		this.aliasIndex = index;
	}

	@Override
	public boolean equalType(PersistAttribute attribute, Column column) {
		boolean result = false;
		boolean scaled = true;
		if (attribute.getType() == null) {
			return true;
		}
		switch (attribute.getType()) {
			case BIGDECIMAL:
				result = "DECIMAL".equalsIgnoreCase(column.dataTypeName);
				break;
			case LONG:
				result = "DECIMAL".equalsIgnoreCase(column.dataTypeName);
				scaled = false;
				break;
			case BOOLEAN:
				result = "DECIMAL".equalsIgnoreCase(column.dataTypeName);
				break;
			case INT:
				result = "DECIMAL".equalsIgnoreCase(column.dataTypeName);
				scaled = false;
				break;
			case STRING:
				result = "VARCHAR".equalsIgnoreCase(column.dataTypeName);
				break;
			case CHAR:
				result = "CHAR".equalsIgnoreCase(column.dataTypeName);
				break;
			case BLOB:
				result = "BLOB".equalsIgnoreCase(column.dataTypeName);
				break;
			case CLOB:
				result = "CLOB".equalsIgnoreCase(column.dataTypeName);
				break;
			case TIMESTAMP:
				result = "DATE".equalsIgnoreCase(column.dataTypeName) || "TIMESTAMP".equalsIgnoreCase(column.dataTypeName);
				scaled = false;
				break;
		}
		result = checkScale(attribute, column, result, scaled);
		return result;
	}

	protected boolean checkScale(PersistAttribute attribute, Column column, boolean result, boolean scaled) {
		if (scaled) {
			if (result && (attribute.getScale() > 0)) {
				result = (column.scale == attribute.getScale());
			}
			if (result && attribute.getPrecision() > 0) {
				result = (attribute.getPrecision() == column.precision);
			}
		}
		if (result) {
			result = attribute.isNullable() == column.nullable();
		}
		return result;
	}

	@Override
	public String getContextId() {
		return contextId;
	}

	public String managePage(DataSnip dataSnip, String sqlClause) {
		return sqlClause;
	}

	public void managePageParams(DataSnip dataSnip, List<Object> paramList) {
	}

	public Snip buildUpdateColumnSnip(String entity, PersistAttribute attribute) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		EntityBundle bundle = DSMapper.getBundle(entity);
		DataSnip create = DataSnipFactory.create(entity);
		create.setBundle(bundle);
		return new Snip(null, SnipType.EXECUTE, MessageFormat.format(SnipUtils.ALTER_TABLE_CHANGE, bundle.getTableName(), buildColumnClause(attribute)), null, null, null, -1, null);
	}

	@Override
	public Snip buildUpdateColumnSnip(String entity, String columName, String typeName, int scale, int precision, boolean nullable) {
		EntityBundle bundle = DSMapper.getBundle(entity);
		String size = buildDataLength(scale, precision);
		String str = SnipUtils.SP + columName + SnipUtils.SP + typeName + SnipUtils.SP + size + SnipUtils.SP + (nullable ? SnipUtils.NN : SnipUtils.NOT_NULL);
		return new Snip(null, SnipType.EXECUTE, MessageFormat.format(SnipUtils.ALTER_TABLE_CHANGE, bundle.getTableName(), str), null, null, null, -1, null);
	}

	@Override
	public String dropKey(MetaLink link) {
		String keyClause = MessageFormat.format(SnipUtils.DROP_FK, link.FKTABLE_NAME, link.FK_NAME);
		return keyClause;
	}

	public Snip buildCreateColumnSnip(String entity, PersistAttribute attribute) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		EntityBundle bundle = DSMapper.getBundle(entity);
		DataSnip create = DataSnipFactory.create(entity);
		create.setBundle(bundle);
		return new Snip(null, SnipType.EXECUTE, MessageFormat.format(SnipUtils.ALTER_TABLE_ADD, bundle.getTableName(), buildColumnClause(attribute)), null, null, null, -1, null);
	}

	@Override
	public synchronized Snip build(DataSnip snip, String transactionId) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		init(snip);
		Snip result = null;
		switch (snip.getSnipType()) {
			case GROUP:
				result = new Snip(null, SnipType.GROUP, null, null, null, buildFetch(snip, transactionId), -1, transactionId);
				break;
			case CREATE:
				result = buildCreateSnip(snip);
				break;
			default:
				switch (snip.getChildPolicy()) {
					case ONLY_THIS:
						result = buildCommonSnip(snip, transactionId);
						break;
					case ONLY_CHILDREN:
						buildChildGroup(snip);
						result = new Snip(null, SnipType.GROUP, null, null, null, buildFetch(snip, transactionId), -1, transactionId);
						break;
					case THIS_AND_CHILDREN:
						buildChildGroup(snip);
						result = buildCommonSnip(snip, transactionId);
						break;
				}
				break;
		}
		clear();
		// initBuilders();
		return result;
	}

	/**
	 * Builds a create snip may be overridden because in some databases dont understand clause with semicolons
	 * 
	 * @param snip
	 * @return Snip
	 * @throws EntityNotMappedException
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws BuildClauseException
	 */
	@Override
	public Snip buildCreateSnip(DataSnip snip) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		buildCreateColumnsClause(snip);
		buildKeysClause(snip);
		buildIndexClause(snip);
		String caseSensitiveTableName = getCaseSensitiveTableName(snip.bundle.getTableName());
		Snip mainSnip = new Snip(null, SnipType.CREATE, MessageFormat.format(SnipUtils.CREATE_TABLE, caseSensitiveTableName, columns), null, null, null, -1, null);
		List<Snip> fetchList = new ArrayList<Snip>();
		String[] split = commaTokens.toString().split(SnipUtils.SEMICOLON);
		for (String indexClause : split) {
			if (indexClause.length() > 1) {
				fetchList.add(new Snip(null, SnipType.CREATE, indexClause, null, null, null, -1, null));
			}
		}
		split = commaColumns.toString().split(SnipUtils.SEMICOLON);
		for (String indexClause : split) {
			if (indexClause.length() > 1) {
				fetchList.add(new Snip(null, SnipType.CREATE, indexClause, null, null, null, -1, null));
			}
		}
		mainSnip.setFetch(fetchList.toArray(new Snip[fetchList.size()]));
		return mainSnip;
	}

	protected Snip buildCommonSnip(DataSnip snip, String transactionId) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		Snip snip2 = new Snip(snip.entityName, snip.getSnipType(), buildQuery(snip), customParamTypes(params), snip.columnNames, buildFetch(snip, transactionId), snip.parentIndex, transactionId);
		snip2.fromRow = snip.fromRow;
		snip2.fetchSize = snip.fetchSize;
		return snip2;
	}

	private void buildChildGroup(DataSnip snip) throws EntityNotMappedException {
		for (EntityBundle child : snip.bundle.childList) {
			snip.fetch(new DataSnip(child.entity, snip.getSnipType()));
		}
	}

	public Object[][] customParamTypes(List<?> paramList) {
		return new Object[][] {
			paramList.toArray()
		};
	}

	public Snip[] buildFetch(DataSnip snip, String transactionId) throws EntityNotMappedException, SecurityException, NoSuchMethodException {
		Snip[] snipFetch = null;
		if (snip.bundle != null) {
			for (EntityPart part : snip.bundle.partList) {
				SnipType snipType = snip.getSnipType();
				if (snipType.equals(SnipType.SELECT)) {
					snip.fetch(new DataSnip(part.getJoinAttribute(), part.getEntity(), part.isJoinFromChild(), snipType, true));
				}
			}
		}
		if (snip.fetchList.size() > 0) {
			int a = 0;
			snipFetch = new Snip[snip.fetchList.size()];
			for (DataSnip fetch : snip.fetchList) {
				snipFetch[a++] = build(fetch, transactionId);
			}
		}
		return snipFetch;
	}

	private String buildQuery(DataSnip snip) throws EntityNotMappedException {
		String result = null;
		switch (snip.getSnipType()) {
			case SELECT:
				if (snip.fetchSize > 0) {
					result = managePage(snip, buildSelect(snip));
				} else {
					result = buildSelect(snip);
				}
				break;
			case DELETE:
				result = buildDelete(snip);
				break;
			case INSERT:
				result = buildInsert(snip);
				break;
			case UPDATE:
				result = buildUpdate(snip);
				break;
			case DROP:
				result = buildDrop(snip);
				break;
			case FUNCTION:
				result = buildFunction(snip);
				break;
			case EXECUTE:
				result = snip.sqlClause;
				break;
			default:
				result = buildSelect(snip);
				break;
		}
		return result;
	}

	protected void addParam(DBType type, Object object) {
		params.add(object);
		// if (object == null) {
		// paramsTypes.add(TypeUtils.getSqlType(type));
		// } else {
		// paramsTypes.add(null);
		// }
	}

	@Override
	public String getCaseSensitiveTableName(String tableName) {
		return tableName.toUpperCase();
	}

	@Override
	public String getDbTypeString(DBType type) {
		return TypeUtils.getDbTypeString(type);
	}

	protected String buildDataLength(int scale, int prescision) {
		String dataLength = SnipUtils.NN;
		if (scale != 0) {
			String dataPrecision = prescision == 0 ? SnipUtils.NN : prescision + "";
			dataLength = SnipUtils.LB + scale + (((dataPrecision.length() == 0) || (dataPrecision.equalsIgnoreCase(SnipUtils.ZERO))) ? SnipUtils.NN : SnipUtils.COMMA + dataPrecision) + SnipUtils.RB;
		}
		return dataLength;
	}

	@SuppressWarnings("nls")
	public String buildColumnClause(PersistAttribute attribute) {
		String typeStr = getDbTypeString(attribute.getType());
		String size = buildDataLength(attribute.getScale(), attribute.getPrecision());
		return SnipUtils.SP + attribute.getColumnName() + SnipUtils.SP + typeStr + SnipUtils.SP + size + (attribute.isNullable() ? SnipUtils.NN : SnipUtils.NOT_NULL);
	}

	public String buildMetaColumnClause(MetaColumn column) {
		String typeStr = column.TYPE_NAME;
		String size = "";
		if ("DECIMAL".equalsIgnoreCase(column.TYPE_NAME)) {
			size = "(22,2)";
		} else if ("DOUBLE".equalsIgnoreCase(column.TYPE_NAME)) {
			size = "(22,2)";
		} else if ("VARCHAR".equalsIgnoreCase(column.TYPE_NAME)) {
			if (column.COLUMN_SIZE == 0) {
				size = "(" + DSSettings.getDefaultStringLength() + ")";
			} else {
				size = buildDataLength(column.COLUMN_SIZE, column.DECIMAL_DIGITS);
			}
		} else if ("CHAR".equalsIgnoreCase(column.TYPE_NAME)) {
			if (column.COLUMN_SIZE == 0) {
				size = "(" + DSSettings.getDefaultStringLength() + ")";
			} else {
				size = buildDataLength(column.COLUMN_SIZE, column.DECIMAL_DIGITS);
			}
		} else if ("BLOB".equalsIgnoreCase(column.TYPE_NAME)) {
			if (column.COLUMN_SIZE != 0) {
				size = column.COLUMN_SIZE + "";
			}
		} else if ("CLOB".equalsIgnoreCase(column.TYPE_NAME)) {
			if (column.COLUMN_SIZE == 0) {
				size = "(6000)";
			}
		}
		return SnipUtils.SP + column.COLUMN_NAME + SnipUtils.SP + typeStr + SnipUtils.SP + size + (column.IS_NULLABLE ? SnipUtils.NN : SnipUtils.NOT_NULL);
	}
}
